﻿using MySql.Data.MySqlClient;
using System.Data.OleDb;
using System.Collections.Generic;
using System;
using System.Collections;

namespace InvoiceManagement.Support
{
  public class DatabaseOperations
  {
    private static MySqlConnection Connection;
    private static MySqlCommand Command;

    static public void ExecuteQueryWithParameters(string query, List<MySqlParameter> parameters)
    {
      try
      {
        DatabaseOperations.Connection.Open();
        DatabaseOperations.Command = new MySqlCommand();
        DatabaseOperations.Command.Connection = DatabaseOperations.Connection;
        DatabaseOperations.Command.CommandText = query;
        DatabaseOperations.Command.Prepare();

        foreach (MySqlParameter p in parameters)
          DatabaseOperations.Command.Parameters.Add(p);

        DatabaseOperations.Command.ExecuteNonQuery();

      }
      catch (Exception e)
      {
        throw e;
      }
      finally
      {
        Connection.Close();
        DatabaseOperations.Command.Parameters.Clear();
      }
    }

    static public ArrayList ExecuteQueryWithParametersGetArrayList(string query, List<MySqlParameter> parameters)
    {
      try
      {
        DatabaseOperations.Connection.Open();
        DatabaseOperations.Command = new MySqlCommand();
        DatabaseOperations.Command.Connection = DatabaseOperations.Connection;
        DatabaseOperations.Command.CommandText = query;
        DatabaseOperations.Command.Prepare();

        foreach (MySqlParameter p in parameters)
          DatabaseOperations.Command.Parameters.Add(p);

        ArrayList rowList = new ArrayList();
        MySqlDataReader reader = DatabaseOperations.Command.ExecuteReader();
        while (reader.Read())
        {
          object[] values = new object[reader.FieldCount];
          reader.GetValues(values);
          rowList.Add(values);
        }

        return rowList;
      }
      catch (Exception e)
      {
        throw e;
      }
      finally
      {
        Connection.Close();
        DatabaseOperations.Command.Parameters.Clear();
      }
    }

    static public List<string> ExecuteQueryWithParametersGetOrdinalList(string query, string ordinalName)
    {
      try
      {
        DatabaseOperations.Connection.Open();
        DatabaseOperations.Command = new MySqlCommand();
        DatabaseOperations.Command.Connection = DatabaseOperations.Connection;
        DatabaseOperations.Command.CommandText = query;
        DatabaseOperations.Command.Prepare();

        List<string> rowList = new List<string>();
        MySqlDataReader reader = DatabaseOperations.Command.ExecuteReader();
        while (reader.Read())
        {
          object value = reader[ordinalName];
          rowList.Add((string)value);
        }

        return rowList;
      }
      catch (Exception e)
      {
        throw e;
      }
      finally
      {
        Connection.Close();
        DatabaseOperations.Command.Parameters.Clear();
      }
    }

    static public List<string> ExecuteQueryGetItemsList(string query, string ordinalName)
    {
      try
      {
        DatabaseOperations.Connection.Open();
        DatabaseOperations.Command = new MySqlCommand();
        DatabaseOperations.Command.Connection = DatabaseOperations.Connection;
        DatabaseOperations.Command.CommandText = query;
        DatabaseOperations.Command.Prepare();

        List<string> rowList = new List<string>();
        MySqlDataReader reader = DatabaseOperations.Command.ExecuteReader();
        while (reader.Read())
        {
          object value = reader[ordinalName];
          rowList.Add((string)value);
        }

        return rowList;
      }
      catch (Exception e)
      {
        throw e;
      }
      finally
      {
        Connection.Close();
        DatabaseOperations.Command.Parameters.Clear();
      }
    }

    static public string ExecuteQueryWithParametersGetScalar(string query, List<MySqlParameter> parameters)
    {
      try
      {
        DatabaseOperations.Connection.Open();
        DatabaseOperations.Command = new MySqlCommand();
        DatabaseOperations.Command.Connection = DatabaseOperations.Connection;
        DatabaseOperations.Command.CommandText = query;
        DatabaseOperations.Command.Prepare();

        foreach (MySqlParameter p in parameters)
          DatabaseOperations.Command.Parameters.Add(p);

        //List<string> rowList = new List<string>();
        //MySqlDataReader reader = DatabaseOperations.Command.ExecuteReader();
        //while (reader.Read())
        //{
        //  object value = reader[ordinalName];
        //  rowList.Add((string)value);
        //}

        //return rowList;
        string result = (string)DatabaseOperations.Command.ExecuteScalar();
        return result;
      }
      catch (Exception e)
      {
        throw e;
      }
      finally
      {
        Connection.Close();
        DatabaseOperations.Command.Parameters.Clear();
      }
    }

    static public List<string> ExecuteQueryWithParametersGetItem(string query, List<MySqlParameter> parameters)
    {
      try
      {
        DatabaseOperations.Connection.Open();
        DatabaseOperations.Command = new MySqlCommand();
        DatabaseOperations.Command.Connection = DatabaseOperations.Connection;
        DatabaseOperations.Command.CommandText = query;
        DatabaseOperations.Command.Prepare();

        foreach (MySqlParameter p in parameters)
          DatabaseOperations.Command.Parameters.Add(p);

        List<string> rowList;
        MySqlDataReader reader = DatabaseOperations.Command.ExecuteReader();
        reader.Read();
        object[] values = new object[reader.FieldCount];

        if (reader.HasRows)
        {
          reader.GetValues(values);
        }
        rowList = new List<string>();
        for (int i = 0; i < values.Length; i++)
          rowList.Add(values[i].ToString());
        return rowList;
      }
      catch (Exception e)
      {
        throw e;
      }
      finally
      {
        Connection.Close();
        DatabaseOperations.Command.Parameters.Clear();
      }
    }

    DatabaseOperations(string connectionString)
    {
      DatabaseOperations.Connection = new MySqlConnection(connectionString);
      DatabaseOperations.Command = new MySqlCommand();
      DatabaseOperations.Command.Connection = DatabaseOperations.Connection;
    }

    static public void Initialize(string connectionString)
    {
      DatabaseOperations.Connection = new MySqlConnection(connectionString);
      DatabaseOperations.Command = new MySqlCommand();
      DatabaseOperations.Command.Connection = DatabaseOperations.Connection;
    }
  }
}
